﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
namespace GroupProject
{
    public partial class frmProducts : Form
    {
        public frmProducts()
        {
            InitializeComponent();

        }

        #region sql connect
        static SqlConnection con;
        static SqlCommand cmd;
        #endregion

        void InitializeAOObject()
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;
        }

        public bool valid() //chua xong
        {
            bool valid = true;
            if (txtProductName.Text.Length == 0 || txtProductName.Text.Trim().Equals(""))
            {
                errorProvider1.Clear();
                errorProvider1.SetError(txtProductName, "Product name cannot be null");
                return false;
            }

            string UnitPrice = txtUnitPrice.Text;
            double UP;
            if (txtUnitPrice.Text.Length == 0 || txtUnitPrice.Text.Trim().Equals(""))
            {
                errorProvider1.Clear();
                errorProvider1.SetError(txtUnitPrice, "Unit Price cannot be Null");
                return false;
            }
            else
                if (!(double.TryParse(txtUnitPrice.Text, out UP)))
                {
                    
                    errorProvider1.Clear();
                    errorProvider1.SetError(txtUnitPrice, "Unit price have only number(Format: 0.00)!");
                    return false;
                }
                else
                {
                    double price = double.Parse(txtUnitPrice.Text.Trim());
                    if (price<0)
                    {
                        errorProvider1.Clear();
                        errorProvider1.SetError(txtUnitPrice,"Unit price must >0");
                        return false;
                    }
                }

            if (rbDisTrue.Checked == false && rbDisFalse.Checked == false)
            {
                errorProvider1.Clear();
                errorProvider1.SetError(grbDiscontinued, "Please choose True or False");
                return false;
            }

            if (valid == false)
            {
                return false;
            }
            return true;
        }

        private void loadComboBox()
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;

            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM production.Suppliers", con);
            DataTable dt = new DataTable("Suppliers");
            da.Fill(dt);
            cbSupplier.DataSource = dt;
            cbSupplier.DisplayMember = "companyname";
            cbSupplier.ValueMember = "supplierid";

            da = new SqlDataAdapter("SELECT * FROM production.Categories", con);
            dt = new DataTable("Categories");
            da.Fill(dt);
            cbCategory.DataSource = dt;
            cbCategory.DisplayMember = "categoryname";
            cbCategory.ValueMember = "categoryid";

        }

        void loadData()
        {
            try
            {
                InitializeAOObject();

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_SelectFromtblProducts";
                SqlDataReader dr = cmd.ExecuteReader();
                dgvProducts.Rows.Clear();
                while (dr.Read())
                {
                    dgvProducts.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(),
                        dr[5].ToString(), dr[6].ToString(), dr[7].ToString());
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }

            dgvProducts.Columns[2].Visible = false;
            dgvProducts.Columns["supplierid"].DisplayIndex = 6; //ban dau la 2
            dgvProducts.Columns["Supplier"].DisplayIndex = 2; //ban dau la 6
            dgvProducts.Columns["categoryid"].Visible = false;
            dgvProducts.Columns["categoryid"].DisplayIndex = 7; //ban dau la 3
            dgvProducts.Columns["Category"].DisplayIndex = 3; //ban dau la 7
        }

        bool addProducts()
        {
            bool result;
            try
            {
                InitializeAOObject();

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_InsertToTblProducts";

                SqlParameter para = new SqlParameter("@productname", SqlDbType.NVarChar);
                para.Size = 40;
                para.Value = txtProductName.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@supplierid", SqlDbType.Int);
                para.Value = cbSupplier.SelectedValue.ToString();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@categoryid", SqlDbType.Int);
                para.Value = cbCategory.SelectedValue.ToString();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@unitprice", SqlDbType.Money);
                para.Value = txtUnitPrice.Text;
                cmd.Parameters.Add(para);

                //para = new SqlParameter("@discontinued", SqlDbType.Bit);
                cmd.Parameters.Add("@discontinued", SqlDbType.Bit).Value = (rbDisTrue.Checked ? 1 : 0);

                cmd.ExecuteNonQuery();
                result = true;


            }
            catch (Exception)
            {
                //MessageBox.Show(ex.Message);
                result = false;
            }
            finally
            {
                con.Close();
            }

            if (result == false)
            {
                return false;
            }
            return true;
        }

        bool UpdateProducts()
        {
            bool result;
            try
            {
                InitializeAOObject();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_UpdateTblProducts";

                SqlParameter para = new SqlParameter("productid", SqlDbType.Int);
                para.Value = txtProductID.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@productname", SqlDbType.NVarChar);
                para.Size = 40;
                para.Value = txtProductName.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@supplierid", SqlDbType.Int);
                para.Value = cbSupplier.SelectedValue.ToString();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@categoryid", SqlDbType.Int);
                para.Value = cbCategory.SelectedValue.ToString();
                cmd.Parameters.Add(para);

                para = new SqlParameter("@unitprice", SqlDbType.Money);
                para.Value = txtUnitPrice.Text;
                cmd.Parameters.Add(para);

                cmd.Parameters.Add("@discontinued", SqlDbType.Bit).Value = (rbDisTrue.Checked ? 1 : 0);

                cmd.ExecuteNonQuery();
                result = true;


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                result = false;
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        bool deleteProducts()
        {
            bool result = true;
            try
            {
                InitializeAOObject();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_DeleteProducts";

                SqlParameter para = new SqlParameter("@productID", SqlDbType.Int);
                para.Value = Int32.Parse(txtProductID.Text);
                cmd.Parameters.Add(para);
                cmd.ExecuteNonQuery();
                result = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        void newProduct()
        {
            errorProvider1.Clear();
            txtProductID.Clear();
            txtProductName.Clear();
            cbCategory.SelectedIndex = -1;
            cbSupplier.SelectedIndex = -1;
            txtUnitPrice.Clear();
            rbDisFalse.Checked = false;
            rbDisTrue.Checked = false;
            txtProductName.Focus();
        }

        void searchByID()
        {
            try
            {
                if (txtSearch.Text.Length == 0)
                {
                    MessageBox.Show("Please enter an key");
                    return;
                }
                InitializeAOObject();

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_SearchProducts";

                SqlParameter para = new SqlParameter("@productid", SqlDbType.Int);
                para.Value = txtSearch.Text;

                cmd.Parameters.Add(para);
                String returnID = cmd.ExecuteScalar().ToString();

                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_SelectFromtblProducts";
                SqlDataReader dr = cmd.ExecuteReader();
                dgvProducts.Rows.Clear();
                while (dr.Read())
                {
                    if (dr[0].ToString().Equals(returnID))
                    {
                        dgvProducts.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(),
                            dr[6].ToString(), dr[7].ToString());
                    }
                }
                dr.Close();

            }
            catch (Exception)
            {
                MessageBox.Show("ID " + txtSearch.Text + " is not exist");
                dgvProducts.Rows.Clear();

            }
            finally
            {
                con.Close();
            }
        }

        void searchByName()
        {
            try
            {
                if (txtSearch.Text.Length == 0)
                {
                    MessageBox.Show("Please Enter an key");
                    return;
                }
                InitializeAOObject();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_SearchProductsName";

                SqlParameter para = new SqlParameter("productname", SqlDbType.NVarChar);
                para.Size = 40;
                para.Value = txtSearch.Text;

                cmd.Parameters.Add(para);
                String returnName = cmd.ExecuteScalar().ToString();

                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_SelectFromtblProducts";
                SqlDataReader dr = cmd.ExecuteReader();
                dgvProducts.Rows.Clear();
                while (dr.Read())
                {
                    if (dr[1].ToString().Equals(returnName))
                    {
                        dgvProducts.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), dr[5].ToString(),
                            dr[6].ToString(), dr[7].ToString());
                    }
                }
                dr.Close();
            }
            catch (Exception)
            {
                MessageBox.Show(txtSearch.Text + " is not Exist in Data");
                dgvProducts.Rows.Clear();
                loadData();
            }
            finally
            {
                con.Close();
            }
        }

        #region event
        private void frmProducts_Load(object sender, EventArgs e)
        {
            InitializeAOObject();
            loadData();
            loadComboBox();
            newProduct();

            List<string> cbSearchData = new List<string>();
            cbSearchData.Add("--Choose search option--");
            cbSearchData.Add("PRODUCT ID");
            cbSearchData.Add("PRODUCT NAME");
            cbSearch.DataSource = cbSearchData;
        }

        private void dgvProducts_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            txtProductID.Text = dgvProducts.CurrentRow.Cells[0].Value.ToString();
            txtProductName.Text = dgvProducts.CurrentRow.Cells[1].Value.ToString();
            cbSupplier.Text = dgvProducts.CurrentRow.Cells[6].Value.ToString();
            cbCategory.Text = dgvProducts.CurrentRow.Cells[7].Value.ToString();
            txtUnitPrice.Text = dgvProducts.CurrentRow.Cells[4].Value.ToString();
            if (dgvProducts.CurrentRow.Cells[5].Value.Equals("True"))
            {
                rbDisTrue.Checked = true;
            }
            else
            {
                rbDisFalse.Checked = true;
            }

            dgvProducts.ReadOnly = true;
            dgvProducts.MultiSelect = false;
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            bool check;

            if (!valid())
            {
                return;
            }

            check = addProducts();
            if (check == true)
            {
                MessageBox.Show("Added");
                newProduct();
            }
            else
            {
                MessageBox.Show("add Fail");
                newProduct();
            }
            loadData();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            bool del;
            if (MessageBox.Show("Are you sure to delete?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                del = deleteProducts();
                if (del == true)
                {
                    loadData();
                    newProduct();
                    MessageBox.Show("Deleted");
                }
                else
                {
                    loadData();
                    newProduct();
                    MessageBox.Show("Delete failed");
                }
            }
            
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            bool chk;
            if (valid())
            {
                try
                {
                    chk = UpdateProducts();
                    if (chk == true)
                    {
                        MessageBox.Show("Update successfull");
                        loadData();
                        newProduct();
                    }
                    else
                    {
                        MessageBox.Show("Update failed");
                        loadData();
                        newProduct();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            newProduct();
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (cbSearch.Text == "--Choose search option--")
                MessageBox.Show("Please Choose search option");
            else
                if (cbSearch.Text == "PRODUCT ID")
                {
                    searchByID();
                }
                else
                    if (cbSearch.Text == "PRODUCT NAME")
                    {
                        searchByName();
                    }
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            InitializeAOObject();
            loadData();
            newProduct();
        }

        private void frmProducts_FormClosing(object sender, FormClosingEventArgs e)
        {
            DialogResult rs;
            rs = MessageBox.Show("Are you sure", "Exit", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (rs == DialogResult.No)
            {
                e.Cancel = true;
            }
        }
        #endregion
    }
}
